﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Runda.CMS.Common.Entity;
using System.Data.Common;
using System.Data;

namespace Runda.CMS.Dal
{
    public partial class WsmInstrumentRecordDal
    {
        public DataTable GetInstrumentRecord(string beginDT,string endDT,string WhereClause, string OrderBy, int PageSize, int PageIndex, out int RecordCount)
        {
            string sqlSb = @"select z.RecordID,z.InstrumentID,z.RecordDate,z.TotalNum,z.DailyNum,z.MaintainID,z.SerialNum,z.CustomerID
                ,z.CustomerName,z.CustomerArea,z.RecorderName,z.instrumentName,z.CreatedOn,ab.Num  from 
                (select a.RecordID,a.InstrumentID,a.RecordDate,a.TotalNum,cast(a.DailyNum as int) as DailyNum,a.MaintainID,a.SerialNum,a.CustomerID
                ,b.CustomerName,b.CustomerArea,e.UserName as RecorderName,f.instrumentName,a.CreatedOn 
                   from Cms_Wsm_InstrumentRecord a 
                    inner join vw_CustomerAllInfo as b on b.CustomerID = a.CustomerID 
                    inner join Cms_Sys_User e on e.UserID = a.Recorder  
                    inner join Cms_Wsm_InstrumentInfo f on f.InstrumentID = a.InstrumentID
 where a.Deleted = 0 and a.SerialNum is not null and a.RecordDate between '{0}' and '{1}' )  Z 
left join (
select c1.CustomerArea,cast(AVG(c2.dailyNum) as int) as Num from Cms_Wsm_InstrumentRecord c2
inner join vw_CustomerAllInfo c1 on c2.CustomerID = c1.CustomerID
where c1.CustomerArea is not null and  c2.RecordDate between '{0}' and '{1}' and c2.dailyNum <> 0 
group by c1.CustomerArea) ab on ab.CustomerArea = z.CustomerArea
where 1=1 {2}";
            string sqlStr = string.Format(sqlSb.ToString(),beginDT,endDT, WhereClause);

            string CmdText = @"WITH T AS
(
SELECT ROW_NUMBER() OVER(ORDER BY {2} ) AS row_number, * 
  FROM ({0}) as A
)
SELECT * from T WHERE row_number > @StartRowNum AND  row_number <= @EndRowNum
select count(*) from ({1}) as B 
";

            CmdText = string.Format(CmdText, sqlStr, sqlStr, OrderBy);

            DbCommand dbCommand = this._db.GetSqlStringCommand(CmdText);
            CurrentDatabase.AddInParameter(dbCommand, "@StartRowNum", System.Data.DbType.Int32, (PageIndex - 1) * PageSize);
            CurrentDatabase.AddInParameter(dbCommand, "@EndRowNum", System.Data.DbType.Int32, PageIndex * PageSize);

            DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);
            RecordCount = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
            return ds.Tables[0];
        }

        public DataTable GetInstrumentRecordByConfirmParms(string SerialNum, Guid InstrumentID, Guid CustomerID)
        {
            string sql = " select * from  Cms_Wsm_InstrumentRecord where deleted = 0 and  SerialNum =@SerialNum and  InstrumentID=@InstrumentID and CustomerID=@CustomerID    order by RecordDate desc ";
            DbCommand dbCommand = this._db.GetSqlStringCommand(sql);
            CurrentDatabase.AddInParameter(dbCommand, "@SerialNum", System.Data.DbType.String, SerialNum);
            CurrentDatabase.AddInParameter(dbCommand, "@InstrumentID", System.Data.DbType.Guid, InstrumentID);
            CurrentDatabase.AddInParameter(dbCommand, "@CustomerID", System.Data.DbType.Guid, CustomerID);
            DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);
            return ds.Tables[0];
        }
    }
}
